package cn.edu.szh.dao;

import cn.edu.szh.bean.StudentInfo;
import cn.edu.szh.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public static boolean add(StudentInfo studentInfo) {
        Connection conn = DBUtil.getConnection();
        String  sql = "insert into student_info values(?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,studentInfo.getGrade_id());
            ps.setInt(2,studentInfo.getClass_id());
            ps.setInt(3,studentInfo.getStudent_id());
            ps.setString(4,studentInfo.getName());
            ps.setString(5,studentInfo.getSex());
            ps.setString(6,studentInfo.getCollege());
            ps.setString(7,studentInfo.getMajor());
            ps.setInt(8,studentInfo.getIntime());
            ps.setString(9,studentInfo.getId_num());
            ps.setString(10,studentInfo.getStatus());
            ps.executeUpdate();
            return true;
        }
        catch (SQLException e){
            e.printStackTrace();
            System.out.println("添加失败");
            return false;
        }finally {
            DBUtil.closeConnection(conn);
        }
    }

    public static boolean update(StudentInfo studentInfo) {
        Connection conn = DBUtil.getConnection();
        String sql = "update student_info set grade_id=?,class_id=?,student_id=?,name=?,sex=?,college=?,major=?,intime=?,id_num=?,status=? where student_id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,studentInfo.getGrade_id());
            ps.setInt(2,studentInfo.getClass_id());
            ps.setInt(3,studentInfo.getStudent_id());
            ps.setString(4,studentInfo.getName());
            ps.setString(5,studentInfo.getSex());
            ps.setString(6,studentInfo.getCollege());
            ps.setString(7,studentInfo.getMajor());
            ps.setInt(8,studentInfo.getIntime());
            ps.setString(9,studentInfo.getId_num());
            ps.setString(10,studentInfo.getStatus());
            ps.setInt(11,studentInfo.getStudent_id());
            ps.executeUpdate();
            return true;
        }
        catch (SQLException e) {
            e.printStackTrace();
            System.out.println("添加用户失败");
            return false;
        }
    }

    public static boolean delete(int studentId) {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from student_info where student_id = ?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,studentId);
            int row= ps.executeUpdate();
            // int row1= ps.executeUpdate();
            if(row==1){
                return true;
            }else{
                System.out.println("不存在学号为"+studentId+"的学生");
                return false;
            }
        }catch (SQLException e){
            e.printStackTrace();
            System.out.println("删除失败");
            return false;
        }finally {
            DBUtil.closeConnection(conn);
        }
    }

    public List<StudentInfo> getAll() {
        List<StudentInfo> list = new ArrayList<>();
        //查询数据库，返回用户信息
        Connection conn = DBUtil.getConnection();
        String sql = "select * from student_info";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                StudentInfo studentInfo = new StudentInfo();
                studentInfo.setGrade_id(rs.getInt("grade_id"));
                studentInfo.setClass_id(rs.getInt("class_id"));
                studentInfo.setStudent_id(rs.getInt("student_id"));
                studentInfo.setName(rs.getString("name"));
                studentInfo.setSex(rs.getString("sex"));
                studentInfo.setCollege(rs.getString("college"));
                studentInfo.setMajor(rs.getString("major"));
                studentInfo.setIntime(rs.getInt("intime"));
                studentInfo.setId_num(rs.getString("id_num"));
                studentInfo.setStatus(rs.getString("status"));
                list.add(studentInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询用户失败");
        }
        DBUtil.closeConnection(conn);
        return list;
    }
}
